<?php
function get_exams($date, $start_time, $end_time) {
	$res = DataBase::query("SELECT * FROM exam WHERE exam_date='".$date."' AND ((exam_start_time>='".$start_time."' AND exam_start_time<'".$end_time."') OR (exam_end_time>='".$start_time."' AND exam_end_time<'".$end_time."') OR (exam_start_time<'".$start_time."' AND exam_end_time>='".$end_time."'))");
	if ($res == false) return array();
	return $res;
}
function get_interviews($date, $start_time, $end_time) {
	$res = DataBase::query("SELECT * FROM interview WHERE interview_date='".$date."' AND ((interview_start_time>='".$start_time."' AND interview_start_time<'".$end_time."') OR (interview_end_time>='".$start_time."' AND interview_end_time<'".$end_time."') OR (interview_start_time<'".$start_time."' AND interview_end_time>='".$end_time."'))");
	if ($res == false) return array();
	return $res;
}

function get_busy_staff($date, $start_time, $end_time, $role=null, $sort_by=null) {
	$exams = get_exams($date, $start_time, $end_time);
	$interviews = get_interviews($date, $start_time, $end_time);
	$q_exams = "";
	foreach ($exams as $e) {
		if ($q_exams<>"") $q_exams .= " OR ";
		$q_exams .= "supervisor.exam_id=".$e["exam_id"];
	}
	$q_interviewers = "";
	$q_observers = "";
	foreach ($interviews as $i) {
		if ($q_interviewers<>"") $q_interviewers .= " OR ";
		if ($q_observers<>"") $q_observers .= " OR ";
		$q_interviewers .= "interviewer.interview_id=".$i["interview_id"];
		$q_observers .= "interview_observer.interview_id=".$i["interview_id"];
	}
	$q = "SELECT * FROM staff";
	$q .= " LEFT JOIN department ON staff.department_id=department.department_id";
	$q .= " LEFT JOIN supervisor ON supervisor.supervisor_username=staff.username";
	$q .= " LEFT JOIN interviewer ON interviewer.interviewer_username=staff.username";
	$q .= " LEFT JOIN interview_observer ON interview_observer.observer_username=staff.username";
	$where = "";
	if ($q_exams <> "") { $where .= "(".$q_exams.")"; }
	if ($q_interviewers <> "") { if ($where<>"") $where .= " OR "; $where .= "(".$q_interviewers.")"; }
	if ($q_observers <> "") { if ($where<>"") $where .= " OR "; $where .= "(".$q_observers.")"; }
	if ($where<>"") $where = "(".$where.")";
	if ($role <> null) { 
		$q .= " LEFT JOIN staff_roles ON staff_roles.username=staff.username";
		if ($where<>"") $where .= " AND "; 
		$where .= "staff_roles.role='".$role."'";
	}
	if ($where<>null) $q .= " WHERE ".$where;
	if ($sort_by <> null) $q .= " ORDER BY ".$sort_by;
	$res = DataBase::query($q);
	if ($res == false) $res = array();
	return $res;
}

function get_free_staff($date, $start_time, $end_time, $role=null, $sort_by=null) {
	$busy = get_busy_staff($date, $start_time, $end_time, $role);
	$where = "";
	$q = "SELECT * FROM staff LEFT JOIN department ON staff.department_id=department.department_id";
	if ($role<>null) {
		$q .= " LEFT JOIN staff_roles ON staff_roles.username=staff.username";
		if ($where <> "") $where .= " AND ";
		$where .= "staff_roles.role='".$role."'";
	}
	if (count($busy)>0) {
		if ($where <> "") $where .= " AND ";
		$where .= "(";
		for ($i = 0; $i < count($busy); $i++) {
			if ($i > 0) $where .= " AND ";
			$where .= "staff.username<>'".$busy[$i]["username"]."'";
		}
		$where .= ")";
	}
	if ($where<>"") $q .= " WHERE ".$where;
	if ($sort_by <> null) $q .= " ORDER BY ".$sort_by;
	$res = DataBase::query($q);
	if ($res == false) $res = array();
	return $res;
}

function get_staff_activities($username) {
	$today = getdate();
	$q = "SELECT 'supervisor' AS TYPE, exam.exam_date AS DATE, exam.exam_start_time AS START_TIME, exam.exam_end_time AS END_TIME, exam_center.exam_center_name AS CENTER_NAME, exam_province.province_name AS PROVINCE_NAME";
	$q .= " FROM exam";
	$q .= " LEFT JOIN supervisor ON supervisor.exam_id = exam.exam_id";
	$q .= " LEFT JOIN exam_center ON exam_center.exam_center_id=exam.exam_center_id"; 
	$q .= " LEFT JOIN province AS exam_province ON exam_province.province_id=exam_center.province_id";
	$q .= " WHERE supervisor.supervisor_username = '".$username."'";
	$q .= " UNION (";
	$q .= " SELECT 'interviewer' AS TYPE, interview.interview_date AS DATE, interview.interview_start_time AS START_TIME, interview.interview_end_time AS END_TIME, ic1.interview_center_name AS CENTER_NAME, province_ic1.province_name AS PROVINCE_NAME";
	$q .= " FROM interview";
	$q .= " LEFT JOIN interviewer ON interviewer.interview_id = interview.interview_id";
	$q .= " LEFT JOIN interview_center AS ic1 ON ic1.interview_center_id=interview.interview_center_id"; 
	$q .= " LEFT JOIN province AS province_ic1 ON province_ic1.province_id=ic1.province_id";
	$q .= " WHERE interviewer.interviewer_username = '".$username."'";
	$q .= " )";
	$q .= " UNION (";
	$q .= " SELECT 'observer' AS TYPE, interview.interview_date AS DATE, interview.interview_start_time AS START_TIME, interview.interview_end_time AS END_TIME, ic2.interview_center_name AS CENTER_NAME, province_ic2.province_name AS PROVINCE_NAME";
	$q .= " FROM interview";
	$q .= " LEFT JOIN interview_observer ON interview_observer.interview_id = interview.interview_id";
	$q .= " LEFT JOIN interview_center AS ic2 ON ic2.interview_center_id=interview.interview_center_id"; 
	$q .= " LEFT JOIN province AS province_ic2 ON province_ic2.province_id=ic2.province_id";
	$q .= " WHERE interview_observer.observer_username = '".$username."'";
	$q .= " )";
	$q .= " ORDER BY DATE, START_TIME";
	$activities = DataBase::query($q);
	if ($activities == false) return array();
	// merge same activity with end_time is equals to start_time of next activity
	for ($i = 0; $i < count($activities)-1; $i++) {
		$a1 = &$activities[$i];
		$a2 = &$activities[$i+1];
		if ($a1["TYPE"] <> $a2["TYPE"]) continue;
		if ($a1["DATE"] <> $a2["DATE"]) continue;
		if ($a1["CENTER_NAME"] <> $a2["CENTER_NAME"]) continue;
		if ($a1["PROVINCE_NAME"] <> $a2["PROVINCE_NAME"]) continue;
		if ($a1["END_TIME"] <> $a2["START_TIME"]) continue;
		$a1["END_TIME"] = $a2["END_TIME"];
		array_splice($activities, $i+1, 1);
		$i--;
	}
	return $activities;
}
function get_display_time($time) {
	$t = explode(":",$time);
	return $t[0]."h".$t[1];
}
function get_staff_activities_description($list) {
	$activities = array();
	foreach ($list as $a) {
		$s = $a["DATE"]." from ".get_display_time($a["START_TIME"])." to ".get_display_time($a["END_TIME"]).": ";
		switch ($a['TYPE']) {
			case "supervisor": $s .= "Supervision of exam"; break;
			case "interviewer": $s .= "Interview"; break;
			case "observer": $s .= "Observation of interview"; break;
		}
		$s .= " at center ".$a["CENTER_NAME"]." in ".$a["PROVINCE_NAME"]." province.";
		array_push($activities, $s);
		
	}
	return $activities;
}
?>
